<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Global transaction IDs</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.qos-consistency.html">Service level and consistency</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.cache.html">Cache integration</a></div>
 <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.quickstart.gtid" class="section">
  <h2 class="title">Global transaction IDs</h2>
  <blockquote class="note"><p><strong class="note">Note</strong>: 
   <strong>Version requirement</strong><br />
   <p class="para">
    A client-side global transaction ID injection has been introduced in mysqlnd_ms version 1.2.0-alpha.
    The feature is not required for synchronous clusters, such as MySQL Cluster.
    Use it with asynchronous clusters such as classical MySQL replication.
   </p>
   <p class="para">
    As of MySQL 5.6.5-m8 the MySQL server features built-in global transaction identifiers.
    The MySQL built-in global transaction ID feature is supported by PECL/mysqlnd_ms 1.3.0-alpha or
    later.
   </p>
  </p></blockquote>
  <p class="para">
   PECL/mysqlnd_ms can either use its own global transaction ID emulation or the
   global transaction ID feature built-in to MySQL 5.6.5-m8 or later. From a developer
   perspective the client-side and server-side approach offer the same features with
   regards to service levels provided by PECL/mysqlnd_ms. Their differences
   are discussed in the <a href="mysqlnd-ms.gtid.html" class="link">concepts section</a>.
  </p>
  <p class="para">
   The quickstart first demonstrates the use of the client-side global transaction ID emulation
   built-in to PECL/mysqlnd_ms before its show how to use the server-side counterpart.
   The order ensures that the underlying idea is discussed first.
  </p>
  <p class="para">
   <em class="emphasis">Idea and client-side emulation</em>
  </p>
  <p class="para">
   In its most basic form a global transaction ID (GTID) is a counter in a table on the
   master. The counter is incremented whenever a transaction is committed on the master.
   Slaves replicate the table. The counter serves two purposes. In case of a
   master failure, it helps the database administrator to identify the most recent slave
   for promoting it to the new master. The most recent slave is the one with the
   highest counter value. Applications can use the global transaction ID to search
   for slaves which have replicated a certain write (identified by a global transaction ID)
   already.
  </p>
  <p class="para">
   PECL/mysqlnd_ms can inject SQL for every committed transaction to increment a GTID counter.
   The so created GTID is accessible by the application to identify an applications
   write operation. This enables the plugin to deliver session consistency (read your writes)
   service level by not only querying masters but also slaves which have replicated
   the change already. Read load is taken away from the master.
  </p>
  <p class="para">
   Client-side global transaction ID emulation has some limitations. Please,
   read the <a href="mysqlnd-ms.gtid.html" class="link">concepts section</a>
   carefully to fully understand the principles and ideas
   behind it, before using in production environments. The background knowledge
   is not required to continue with the quickstart.
  </p>
  <p class="para">
   First, create a counter table on your master server and insert a record into it.
   The plugin does not assist creating the table.
   Database administrators must make sure it exists. Depending on the error
   reporting mode, the plugin will silently ignore the lack of the table or bail out.
  </p>
  <p class="para">
   <div class="example" id="example-1747">
    <p><strong>Example #1 Create counter table on master</strong></p>
    <div class="example-contents">
<div class="sqlcode"><pre class="sqlcode">CREATE TABLE `trx` (
  `trx_id` int(11) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO `trx`(`trx_id`) VALUES (1);</pre>
</div>
    </div>

   </div>
  </p>
  <p class="para">
   In the plugins configuration file set the SQL to update the
   global transaction ID table using <em>on_commit</em>
   from the <em>global_transaction_id_injection</em>
   section. Make sure the table name used for the <em>UPDATE</em>
   statement is fully qualified. In the example,
   <em>test.trx</em> is used to refer to table <em>trx</em>
   in the schema (database) <em>test</em>. Use the table that was created in
   the previous step. It is important to set the fully qualified table name
   because the connection on which the injection is done may use a different
   default database. Make sure the user that opens the connection
   is allowed to execute the <em>UPDATE</em>.
  </p>
  <p class="para">
   Enable reporting of errors that may occur when mysqlnd_ms does global
   transaction ID injection.
  </p>
  <p class="para">
   <div class="example" id="example-1748">
    <p><strong>Example #2 Plugin config: SQL for client-side GTID injection</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">{
    &quot;myapp&quot;: {
        &quot;master&quot;: {
            &quot;master_0&quot;: {
                &quot;host&quot;: &quot;localhost&quot;,
                &quot;socket&quot;: &quot;\/tmp\/mysql.sock&quot;
            }
        },
        &quot;slave&quot;: {
            &quot;slave_0&quot;: {
                &quot;host&quot;: &quot;127.0.0.1&quot;,
                &quot;port&quot;: &quot;3306&quot;
            }
        },
        &quot;global_transaction_id_injection&quot;:{
            &quot;on_commit&quot;:&quot;UPDATE test.trx SET trx_id = trx_id + 1&quot;,
            &quot;report_error&quot;:true
        }
    }
}</pre>
</div>
    </div>

   </div>
  </p>
  <p class="para">
   <div class="example" id="example-1749">
    <p><strong>Example #3 Transparent global transaction ID injection</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"username"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br />&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;Of&nbsp;course,&nbsp;your&nbsp;error&nbsp;handling&nbsp;is&nbsp;nicer...&nbsp;*/<br />&nbsp;&nbsp;</span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(),&nbsp;</span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT&nbsp;INTO&nbsp;test(id)&nbsp;VALUES&nbsp;(1)"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;read&nbsp;on&nbsp;slave,&nbsp;no&nbsp;increment&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!(</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;id&nbsp;FROM&nbsp;test"</span><span style="color: #007700">)))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
array(1) {
  [&quot;id&quot;]=&gt;
  string(1) &quot;1&quot;
}
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   The example runs three statements in auto commit mode on the master, causing
   three transactions on the master. For every such statement, the plugin will
   inject the configured <em>UPDATE</em> transparently before executing
   the users SQL statement. When the script ends the global
   transaction ID counter on the master has been incremented by three.
  </p>
  <p class="para">
   The fourth SQL statement executed in the example, a <em>SELECT</em>,
   does not trigger an increment. Only transactions (writes) executed on a master
   shall increment the GTID counter.
  </p>
  <blockquote class="note"><p><strong class="note">Note</strong>: 
   <strong>SQL for global transaction ID: efficient solution wanted!</strong><br />
   <p class="para">
    The SQL used for the client-side global transaction ID emulation is inefficient.
    It is optimized for clearity not for performance. Do not use it for production
    environments. Please, help finding an efficient solution for inclusion in the manual.
    We appreciate your input.
   </p>
  </p></blockquote>
  <p class="para">
   <div class="example" id="example-1750">
    <p><strong>Example #4 Plugin config: SQL for fetching GTID</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">{
    &quot;myapp&quot;: {
        &quot;master&quot;: {
            &quot;master_0&quot;: {
                &quot;host&quot;: &quot;localhost&quot;,
                &quot;socket&quot;: &quot;\/tmp\/mysql.sock&quot;
            }
        },
        &quot;slave&quot;: {
            &quot;slave_0&quot;: {
                &quot;host&quot;: &quot;127.0.0.1&quot;,
                &quot;port&quot;: &quot;3306&quot;
            }
        },
        &quot;global_transaction_id_injection&quot;:{
            &quot;on_commit&quot;:&quot;UPDATE test.trx SET trx_id = trx_id + 1&quot;,
            &quot;fetch_last_gtid&quot; : &quot;SELECT MAX(trx_id) FROM test.trx&quot;,
            &quot;report_error&quot;:true
        }
    }
}</pre>
</div>
    </div>

   </div>
  </p>
  <p class="para">
   <div class="example" id="example-1751">
    <p><strong>Example #5 Obtaining GTID after injection</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"username"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br />&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;Of&nbsp;course,&nbsp;your&nbsp;error&nbsp;handling&nbsp;is&nbsp;nicer...&nbsp;*/<br />&nbsp;&nbsp;</span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(),&nbsp;</span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"GTID&nbsp;after&nbsp;transaction&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqlnd_ms_get_last_gtid</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"GTID&nbsp;after&nbsp;transaction&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqlnd_ms_get_last_gtid</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">));<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
GTID after transaction 7
GTID after transaction 8
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   Applications can ask PECL mysqlnd_ms for a global transaction ID which
   belongs to the last write operation performed by the application.
   The function <span class="function"><a href="function.mysqlnd-ms-get-last-gtid.html" class="function">mysqlnd_ms_get_last_gtid()</a></span> returns the
   GTID obtained when executing the SQL statement from
   the <em>fetch_last_gtid</em> entry of the
   <em>global_transaction_id_injection</em> section from
   the plugins configuration file. The function may be called
   after the GTID has been incremented.
  </p>
  <p class="para">
   Applications are adviced not to run the SQL
   statement themselves as this bares the risk of accidently causing an implicit
   GTID increment. Also, if the function is used, it is easy to migrate
   an application from one SQL statement for fetching a transaction ID to another,
   for example, if any MySQL server ever features built-in global transaction ID support.
  </p>
  <p class="para">
   The quickstart shows a SQL statement which will return a GTID equal or greater
   to that created for the previous statement. It is exactly the GTID created
   for the previous statement if no other clients have incremented the GTID in the
   time span between the statement execution and the <em>SELECT</em>
   to fetch the GTID. Otherwise, it is greater.
  </p>
  <p class="para">
   <div class="example" id="example-1752">
    <p><strong>Example #6 Plugin config: Checking for a certain GTID</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">{
    &quot;myapp&quot;: {
        &quot;master&quot;: {
            &quot;master_0&quot;: {
                &quot;host&quot;: &quot;localhost&quot;,
                &quot;socket&quot;: &quot;\/tmp\/mysql.sock&quot;
            }
        },
        &quot;slave&quot;: {
            &quot;slave_0&quot;: {
                &quot;host&quot;: &quot;127.0.0.1&quot;,
                &quot;port&quot;: &quot;3306&quot;
            }
        },
        &quot;global_transaction_id_injection&quot;:{
            &quot;on_commit&quot;:&quot;UPDATE test.trx SET trx_id = trx_id + 1&quot;,
            &quot;fetch_last_gtid&quot; : &quot;SELECT MAX(trx_id) FROM test.trx&quot;,
            &quot;check_for_gtid&quot; : &quot;SELECT trx_id FROM test.trx WHERE trx_id &gt;= #GTID&quot;,
            &quot;report_error&quot;:true
        }
    }
}</pre>
</div>
    </div>

   </div>
  </p>
  <p class="para">
   <div class="example" id="example-1753">
    <p><strong>Example #7 Session consistency service level and GTID combined</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"username"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br />&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;Of&nbsp;course,&nbsp;your&nbsp;error&nbsp;handling&nbsp;is&nbsp;nicer...&nbsp;*/<br />&nbsp;&nbsp;</span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(),&nbsp;</span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/*&nbsp;auto&nbsp;commit&nbsp;mode,&nbsp;transaction&nbsp;on&nbsp;master,&nbsp;GTID&nbsp;must&nbsp;be&nbsp;incremented&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">)&nbsp;||<br />&nbsp;&nbsp;&nbsp;&nbsp;!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">)&nbsp;||<br />&nbsp;&nbsp;&nbsp;&nbsp;!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT&nbsp;INTO&nbsp;test(id)&nbsp;VALUES&nbsp;(1)"</span><span style="color: #007700">))<br />&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/*&nbsp;GTID&nbsp;as&nbsp;an&nbsp;identifier&nbsp;for&nbsp;the&nbsp;last&nbsp;write&nbsp;*/<br /></span><span style="color: #0000BB">$gtid&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">mysqlnd_ms_get_last_gtid</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/*&nbsp;Session&nbsp;consistency&nbsp;(read&nbsp;your&nbsp;writes):&nbsp;try&nbsp;to&nbsp;read&nbsp;from&nbsp;slaves&nbsp;not&nbsp;only&nbsp;master&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(</span><span style="color: #0000BB">false&nbsp;</span><span style="color: #007700">==&nbsp;</span><span style="color: #0000BB">mysqlnd_ms_set_qos</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">MYSQLND_MS_QOS_CONSISTENCY_SESSION</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">MYSQLND_MS_QOS_OPTION_GTID</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$gtid</span><span style="color: #007700">))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[006]&nbsp;[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #FF8000">/*&nbsp;Either&nbsp;run&nbsp;on&nbsp;master&nbsp;or&nbsp;a&nbsp;slave&nbsp;which&nbsp;has&nbsp;replicated&nbsp;the&nbsp;INSERT&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!(</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;id&nbsp;FROM&nbsp;test"</span><span style="color: #007700">)))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">));<br />}<br /><br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

   </div>
  </p>
  <p class="para">
   A GTID returned from <span class="function"><a href="function.mysqlnd-ms-get-last-gtid.html" class="function">mysqlnd_ms_get_last_gtid()</a></span>
   can be used as an option for the session consistency service level.
   Session consistency delivers read your writes. Session consistency can
   be requested by calling
   <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>.
   In the example, the plugin will execute the <em>SELECT</em>
   statement either on the master or on a slave which has replicated
   the previous <em>INSERT</em> already.
  </p>
  <p class="para">
   PECL mysqlnd_ms will transparently check every configured slave if
   it has replicated the <em>INSERT</em> by checking the slaves
   GTID table. The check is done running the SQL set with the
   <em>check_for_gtid</em> option from the
   <em>global_transaction_id_injection</em> section of
   the plugins configuration file. Please note, that this is a slow and
   expensive procedure. Applications should try to use it sparsely and only
   if read load on the master becomes to high otherwise.
  </p>
  <p class="para">
   <em class="emphasis">Use of the server-side global transaction ID feature</em>
  </p>
  <p class="para">
   Starting with MySQL 5.6.5-m8 the MySQL Replication system features server-side
   global transaction IDs. Transaction identifiers are automatically generated and
   maintained by the server. Users do not need to take care of maintaining them.
   There is no need to setup any tables in advance, or for setting
   <em>on_commit</em>. A client-side emulation is no longer needed.
  </p>
  <p class="para">
    Clients can continue to use global transaction identifier to achieve
    session consistency when reading from MySQL Replication slaves. The algorithm
    works as described above. Different SQL statements must be configured for
    <em>fetch_last_gtid</em> and <em>check_for_gtid</em>.
    The statements are given below. Please note, MySQL 5.6.5-m8 is a development
    version. Details of the server implementation may change in the future and require
    adoption of the SQL statements shown.
  </p>
  <p class="para">
    Using the following configuration any of the above described functionality can
    be used together with the server-side global transaction ID feature.
    <span class="function"><a href="function.mysqlnd-ms-get-last-gtid.html" class="function">mysqlnd_ms_get_last_gtid()</a></span> and <span class="function"><a href="function.mysqlnd-ms-set-qos.html" class="function">mysqlnd_ms_set_qos()</a></span>
    continue to work as described above. The only difference is that the server
    does not use a simple sequence number but a string containing of a server identifier
    and a sequence number. Thus, users cannot easily derive an order from GTIDs returned
    by <span class="function"><a href="function.mysqlnd-ms-get-last-gtid.html" class="function">mysqlnd_ms_get_last_gtid()</a></span>.
  </p>
  <p class="para">
   <div class="example" id="example-1754">
    <p><strong>Example #8 Plugin config: using MySQL 5.6.5-m8 built-in GTID feature</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">{
    &quot;myapp&quot;: {
        &quot;master&quot;: {
            &quot;master_0&quot;: {
                &quot;host&quot;: &quot;localhost&quot;,
                &quot;socket&quot;: &quot;\/tmp\/mysql.sock&quot;
            }
        },
        &quot;slave&quot;: {
            &quot;slave_0&quot;: {
                &quot;host&quot;: &quot;127.0.0.1&quot;,
                &quot;port&quot;: &quot;3306&quot;
            }
        },
        &quot;global_transaction_id_injection&quot;:{
            &quot;fetch_last_gtid&quot; : &quot;SELECT @@GLOBAL.GTID_DONE AS trx_id FROM DUAL&quot;,
            &quot;check_for_gtid&quot; : &quot;SELECT GTID_SUBSET(&#039;#GTID&#039;, @@GLOBAL.GTID_DONE) AS trx_id FROM DUAL&quot;,
            &quot;report_error&quot;:true
        }
    }
}</pre>
</div>
    </div>

   </div>
  </p>
 </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.qos-consistency.html">Service level and consistency</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.cache.html">Cache integration</a></div>
 <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
